<?php
include "User.php";

class UserDAO extends DataLayer {
	/**
	* @desc Construtor da Classe
 	*/
	function __construct($lk){
		$this->lk = $lk;	
	}

	public function getUsers($id=0, $page=0, $typeJoin=false, $status=NULL, $groupSimple=false){

		if($typeJoin){
			$join = "LEFT";
		}else{
			$join = "INNER";
		}

		if($status == NULL)
			$status = array(1);
	
		$this->sql ="	SELECT 
							SQL_CALC_FOUND_ROWS
							u.id_usuario, u.login, u.senha, u.nome, u.email, u.carga_horaria, DATE_FORMAT(u.data_criacao, '%d/%m/%Y') data_criacao, DATE_FORMAT(u.data_saida, '%d/%m/%Y') data_saida, u.status, u.data
							,ug.titulo grupo, ug.id_usuario_grupo idGroup
						FROM 
							usuarios u
						".$join." JOIN
							usuarios_grupos_acl uga on uga.id_usuario = u.id_usuario
						".$join." JOIN
							usuarios_grupos ug on ug.id_usuario_grupo = uga.id_usuario_grupo
						WHERE
							u.status IN (".implode(',',$status).")";

		if($id > 0)						
			$this->sql .= "  AND u.id_usuario = ".$id;

		if($groupSimple && !$typeJoin)
			$this->sql .= "  AND ug.id_usuario_grupo > 2";
		
		$this->sql .= " ORDER BY u.nome ASC";

		return $this->pagination($this->sql, $page);
	}


	public function getUsersTeam($id=0, $idTeam=0, $page=0){
		$this->sql ="	SELECT 
							SQL_CALC_FOUND_ROWS
							u.id_usuario, u.login, u.senha, u.nome, u.email, u.carga_horaria, DATE_FORMAT(u.data_criacao, '%d/%m/%Y') data_criacao, DATE_FORMAT(u.data_saida, '%d/%m/%Y') data_saida, u.status, u.data							
						FROM 
							usuarios u
						INNER JOIN
							equipes_usuarios eu on eu.id_usuario = u.id_usuario						
						WHERE
							u.status = 1";

		if($id > 0)						
			$this->sql .= "  AND u.id_usuario = ".$id;

		if($idTeam > 0)						
			$this->sql .= "  AND eu.id_equipe = ".$idTeam;
		
		$this->sql .= " ORDER BY u.nome ASC";
		
		return $this->pagination($this->sql, $page);
	}

	public function getUsersWithoutGroup(){
		$this->sql ="	SELECT 
							SQL_CALC_FOUND_ROWS
							u.id_usuario, u.login, u.senha, u.nome, u.email, u.carga_horaria, DATE_FORMAT(u.data_criacao, '%d/%m/%Y') data_criacao, DATE_FORMAT(u.data_saida, '%d/%m/%Y') data_saida, u.status, u.data							
						FROM 
							usuarios u
						WHERE
							NOT EXISTS(SELECT ega.id_usuario FROM usuarios_grupos_acl ega WHERE ega.id_usuario = u.id_usuario)
							AND u.status IN (1,0)
						ORDER BY 
							u.nome ASC";
		
		return $this->pagination($this->sql, $page);
	}

	public function pagination($sql, $page){
		$this->sql = $sql;

		if($page > 0){
			$page = (int)$page;
			$page--;
			if($page < 0)
				$page = 0;
			else
				$page *= PAGINATION;

			$this->sql .= " LIMIT ".$page.",".PAGINATION;
		}
		
		$this->result = parent::select($this->sql);
		$userList = NULL;
		
		if(count($this->result) > 0){
			$i = 0;
			foreach($this->result as $row){
				$userList[$i] = new User();				
				$userList[$i]->setDadosDB($row);				

				$i++;
			}
		}
		
		return $userList;
	}

	public function checkEmail($email="", $id=0){
		if($email != ""){
			$this->sql = "SELECT id_usuario FROM usuarios WHERE email = '".$email."'";
			if($id > 0){
				$this->sql .= " id_usuario <>".$id;
			}

			$this->result = parent::select($this->sql);
		
			if(count($this->result) == 0) //Não existe o email no banco
				return 1;
		}else{
			return 0;
		}
	}

	public function checkUser($user="", $id=0){
		if($user != ""){
			$this->sql = "SELECT id_usuario FROM usuarios WHERE login = '".$user."'";
			if($id > 0){
				$this->sql .= " id_usuario <>".$id;
			}

			$this->result = parent::select($this->sql);
		
			if(count($this->result) == 0) // Não existe o user no banco
				return 1;
		}else{
			return 0;
		}
	}

	public function getGroups($allGroups=false){
		$this->sql = "	SELECT 
							ug.id_usuario_grupo idUserGroup, 
							ug.titulo title,
							(	SELECT	
									COUNT(uga.id_usuario) 
								FROM
									usuarios_grupos_acl uga
								INNER JOIN
									usuarios u on u.id_usuario = uga.id_usuario
								WHERE
									u.status IN (1,0) AND uga.id_usuario_grupo = ug.id_usuario_grupo
							) users
						FROM 
							usuarios_grupos ug";
		
		if(!$allGroups){
			$this->sql .= " WHERE ug.id_usuario_grupo > 1";
		}					

		$this->result = parent::select($this->sql);

		return $this->result;
	}

	public function login($login, $pass){
		
		$this->sql 		= "SELECT id_usuario, senha FROM usuarios WHERE login = '".$login."' AND status = 1";
		$this->result 	= parent::select($this->sql);

		if(count($this->result) > 0){			
			if($this->result[0]->senha === $pass){										
				return $this->result[0]->id_usuario;
			}
		}

		return 0;
	}

	public function save(User $obj){
		if($obj != NULL){
			$dbObj = $obj->fieldsDBObj;
			unset($dbObj["id_usuario"]);
			if($obj->id > 0){
				parent::update("usuarios",$dbObj,"id_usuario = ".$obj->id);
				return $obj->id;
			}else{
				return parent::insert("usuarios",$dbObj,"data");
			}
		}else{
			return 0;
		}
	}

	public function deleteUser($id=0){
		$return = 0;

		if($id > 0){
			$this->sql = "	SELECT 
								id_projeto
							FROM
								projetos
							WHERE
								".$id." IN (id_product_owner, id_scrum_master) AND status = 1";
			
			$this->result = parent::select($this->sql);

			if(count($this->result) > 0){
				$return = -1;
			}else{
				$dados["status"] = -1;
				$return = parent::update("usuarios", $dados, "id_usuario=".$id);	
			}
		}

		return $return;
	}

	public function logoff(){
		unset($_SESSION);
		session_unset();
	}
}

?>